﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using System.Data.OleDb;

namespace 实验7_学生成绩管理系统
{
    class DBHelper
    {
        string m_StrConn = "Provider=SQLOLEDB;Data Source=.;Integrated Security=SSPI;Initial Catalog=master";
        OleDbConnection conn = null;
        OleDbCommand cmd = new OleDbCommand();

        string sqlSelectAll
        {
            get;
            set;
        }

        public DBHelper(string strConn)
        {
            m_StrConn = strConn;
        }

        public DBHelper()
        {

        }


        public DataTable GetDataTable(string sql, string[] names, params object[] vals)
        {
            DataTable dt = new DataTable();
            conn = new OleDbConnection(m_StrConn);
            try
            {
                conn.Open();
                cmd = new OleDbCommand();
                cmd.CommandText = sql;
                cmd.Connection = conn;
                AddParams(cmd, vals);
                OleDbDataAdapter adapter = new OleDbDataAdapter();
                adapter.SelectCommand = cmd;
                adapter.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
        }

        public int Execute(OleDbCommand cmd, params object[] vals)
        {
            int rowAffected = -1;
            try
            {
                AddParams(cmd, vals);
                rowAffected = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return rowAffected;
        }


        public int Execute(string sql, params object[] vals)
        {
            int rowAffected = -1;
            conn = new OleDbConnection(m_StrConn);
            try
            {
                conn.Open();
                cmd = new OleDbCommand();
                cmd.CommandText = sql;
                cmd.Connection = conn;
                AddParams(cmd, vals);
                rowAffected = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            return rowAffected;
        }

        public void Execute(DataTable dtSource)
        {
            conn = new OleDbConnection(m_StrConn);
            OleDbTransaction trans = null;
            try
            {
                conn.Open();
                trans = conn.BeginTransaction();
                cmd = new OleDbCommand();
                cmd.Connection = conn;
                cmd.Transaction = trans;
                cmd.Parameters.Clear();
                string sql = "delete from student where sno = ?";
                cmd.CommandText = sql;
                //处理删除
                DataTable dtDeleted = dtSource.GetChanges(DataRowState.Deleted);
                if (dtDeleted != null)
                {
                    foreach (DataRow dr in dtDeleted.Rows)
                    {
                        Execute(cmd, dr["sno", DataRowVersion.Original]);
                    }
                }
                //处理更新数据
                sql = "update student set sno = ?, sname = ?, age = ?, dept = ?, sex = ? where sno = ?";
                cmd.CommandText = sql;
                DataTable dtUpdated = dtSource.GetChanges(DataRowState.Modified);
                if (dtUpdated != null)
                {
                    foreach (DataRow dr in dtUpdated.Rows)
                    {
                        Execute(cmd, dr["sno"], dr["sname"], dr["age"], dr["dept"], dr["sex"], dr["sno", DataRowVersion.Original]);
                    }
                }
                //处理插入数据
                sql = "insert into student (sname, sno, age, dept, sex) values (?, ?, ?, ?, ?)";
                DataTable dtInserted = dtSource.GetChanges(DataRowState.Added);
                cmd.CommandText = sql;
                if (dtInserted != null)
                {
                    foreach (DataRow dr in dtInserted.Rows)
                    {
                        Execute(cmd, dr["sname"], dr["sno"], dr["age"], dr["dept"], dr["sex"]);
                    }
                }
                trans.Commit();
                dtSource.AcceptChanges();
            }
            catch (Exception ex)
            {
                if (trans != null)
                    trans.Rollback();
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        void AddParams(OleDbCommand cmd, params object[] vals)
        {
            cmd.Parameters.Clear();
            if (vals == null)
                return;
            foreach (object v in vals)
            {
                OleDbParameter param = new OleDbParameter();
                param.Value = v;
                cmd.Parameters.Add(param);
            }
        }
    }
}
